﻿DECLARE @idlotto bigint 
DECLARE @numddt varchar(50) 
DECLARE @dataddt datetime
DECLARE @idfattura bigint

DECLARE @numddtKey varchar(50) 
DECLARE @dataddtKey datetime

update [GestionePolveri].[GP].[Lotti] set IDFattura = null
delete [GestionePolveri].[GP].[Fatture]

DECLARE SIMPLE_CURSOR CURSOR FOR 
SELECT idlotto, numddt, dataddt FROM [GestionePolveri].[GP].[Lotti] order by numddt, dataddt

OPEN SIMPLE_CURSOR

FETCH NEXT FROM SIMPLE_CURSOR INTO @idlotto, @numddt, @dataddt

Set @numddtKey = @numddt
Set @dataddtKey = @dataddt

DBCC CHECKIDENT ("GestionePolveri.GP.Fatture", RESEED, 0)
INSERT INTO [GestionePolveri].[GP].[Fatture] ([NumDdt], [DataDdt]) VALUES (@numddt, @dataddt)
SELECT @idfattura = @@IDENTITY
update [GestionePolveri].[GP].[Lotti] set IDFattura = @idFattura where IDLotto = @idLotto
			
WHILE @@FETCH_STATUS = 0
	BEGIN

        if(@numddtKey <> @numddt) -- and (@dataddtKey <> @dataddt))
         Begin
			INSERT INTO [GestionePolveri].[GP].[Fatture] ([NumDdt], [DataDdt]) VALUES (@numddt, @dataddt)
			SELECT @idfattura = @@IDENTITY

			update [GestionePolveri].[GP].[Lotti] set IDFattura = @idFattura where IDLotto = @idLotto
			
		    Set @numddtKey = @numddt
		    Set @dataddtKey = @dataddt
		 End
		else
		 Begin
		    update [GestionePolveri].[GP].[Lotti] set IDFattura = @idFattura where IDLotto = @idLotto
		 End 
		 
		 FETCH NEXT FROM SIMPLE_CURSOR INTO @idlotto, @numddt, @dataddt
	END

CLOSE SIMPLE_CURSOR   
DEALLOCATE SIMPLE_CURSOR